Imports System.Data.SqlClient
Imports SSA.CommonControl.SharedCode
Module Module1

    Sub Main()
        Dim ConnString As String
        Dim LoopCounter As Integer = 0
        Dim Args(3) As String
        Dim SQLCmd As SqlCommand

        If Environment.GetCommandLineArgs.Length = 1 Then ShowHelpMessage()
        For Each arg As String In Environment.GetCommandLineArgs()
            If arg = "/?" Or arg = "?" Then
                ShowHelpMessage()
            End If
            Args(LoopCounter) = arg
            LoopCounter += 1
        Next arg
        Console.WriteLine("Checking for and removing SQL Objects.")
        ConnString = FormatconnectionString(Args(1), Args(2))
        Console.WriteLine(ConnString)
        Try
            If ObjectExists(ConnString, "xp_sqlagent_enum_jobs_SSA", "dbo") = True Then
                Console.WriteLine("Removing xp_sqlagent_enum_jobs_SSA")
                SQLCmd = New SqlCommand
                SQLCmd.CommandText = "DROP PROCEDURE xp_sqlagent_enum_jobs_SSA"
                SQLCmd.CommandType = CommandType.Text
                RunSQLStatement(ConnString, SQLCmd)
            End If
            If ObjectExists(ConnString, "sp_get_composite_job_info_SSA", "dbo") = True Then
                Console.WriteLine("Removing sp_get_composite_job_info_SSA")
                SQLCmd = New SqlCommand
                SQLCmd.CommandType = CommandType.Text
                SQLCmd.CommandText = "DROP PROCEDURE sp_get_composite_job_info_SSA"
                RunSQLStatement(ConnString, SQLCmd)
            End If
            If ObjectExists(ConnString, "sp_help_job_SSA", "dbo") = True Then
                Console.WriteLine("Removing sp_help_job_SSA")
                SQLCmd = New SqlCommand
                SQLCmd.CommandText = "DROP PROCEDURE sp_help_job_SSA"
                SQLCmd.CommandType = CommandType.Text
                RunSQLStatement(ConnString, SQLCmd)
            End If
        Catch ex As Exception
            Console.WriteLine("Error executing command.  Application was not run successfully.")
            Console.WriteLine(ex.Message)
            End
        End Try
        Console.WriteLine("Command completed without error.")

    End Sub
    Sub ShowHelpMessage()
        Console.WriteLine("This application is used to remove the SQL Server database objects from a SQL Server which the Standalone SQL Agent has been run against.")
        Console.WriteLine()
        Console.WriteLine("The Standalone SQL Agent will automatically recreate these objects the next time it is started.")
        Console.WriteLine()
        Console.WriteLine("Usage: ""Clean Up SQL Objects.exe"" Server\Instance msdb")
        Console.WriteLine("Only Windows Authentication can be used with this application.")
        Console.WriteLine()
        Console.WriteLine("Press Enter to Exit.")
        Console.ReadLine()
        End
    End Sub

End Module
